<!DOCTYPE html>
 
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<%@ page import="java.util.Date" %>
<%@ page import="java.text.DateFormat" %>
<%@ page import="java.text.SimpleDateFormat" %>

<%
try {
 
 String connectionURL = "jdbc:mysql://localhost:3306/unilearn";
 
 int c_row_count = 0;
 Connection connection = null;
 
 ResultSet rst=null;
 Class.forName("com.mysql.jdbc.Driver").newInstance();
 connection = DriverManager.getConnection(connectionURL, "root", "");
 Statement stmt = null;
 stmt = connection.createStatement();
 
 if(!connection.isClosed())
 {
  
  
  DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
  DateFormat dateFormat2 = new SimpleDateFormat("yyyy-MM-dd");
  DateFormat timeFormat = new SimpleDateFormat("HH:mm:ss");
  Date _now = new Date();
  
  String subject_value=request.getParameter("cboStudentList");
  String str_tmp[] = subject_value.split(";");
  String subject_id = str_tmp[0];
  String st_user_id = str_tmp[1];
  
  //set selected student user id
  session.setAttribute( "StudentUserID",  st_user_id);
  
  //get session values
  String user_id = (String)session.getAttribute("UserID");
  String permission = (String)session.getAttribute("Permission");
  
 
%>
<html lang="en">
<head>
<script type="text/javascript">
 
</script>
<link rel="stylesheet" type="text/css" href="common.css">
<title>UniLearn System</title>
</head>
<body>
<form action="assignmentCreate.jsp" method="post" name="entry" onSubmit="return validate(this)">
<table class="TblBody" style="width:720px">
 <tr>
  <td>
   <div class="contentb">
    <table width="100%">
                    <tr>
                        <td  style="background-color:#ccff99; font-size:20px; font-weight:bold; width:70%; height:30px">Select Student</td>
      <!-- <td style="background-color:#ccff99; font-weight:bold;"><%= dateFormat2.format(_now) %>&nbsp;&nbsp; Time: <%= timeFormat.format(_now) %></td> -->
                    </tr>
     <tr>
                        <td colspan="2">
       <%
			rst = stmt.executeQuery("select * from assignment where subject_id = '"+subject_id+"' " +
									"and (select count(*) from subject_mapping where subject_id = '"+subject_id+"' and user_id = '"+st_user_id+"')");
       %>
       <table Class="SubTable">
        <tr>
         <th>Assignment</th>
         <th>Due Date</th>
         <th>Uploaded Date</th>
         <th>Action</th>
        </tr>
        <%
         while(rst.next())
         { 
			ResultSet rs = null;
			ResultSet rs1 = null;
			Statement stmt1 = null;
			Statement stmt2 = null;
			
			stmt1 = connection.createStatement();
			stmt2 = connection.createStatement();
			
			String assign_id = rst.getString("assignment_id");
			String assign_name = rst.getString("assignment_name");
			String user_group_id = "";
			rs = stmt1.executeQuery("SELECT user_group_id FROM group_tbl WHERE assignment_id = '"+assign_id+"' AND user_id = '"+st_user_id+"'");
			while(rs.next())
			{
				user_group_id = rs.getString("user_group_id");
			}
          
			if (user_group_id != null && user_group_id != ""){
				rs1 = stmt2.executeQuery("select assignment_name, end_date, " +
									"(select assignment_sub_id from assignment_submission where assignment_id = '"+assign_id+"' and user_group_id = '"+user_group_id+"') as ass_sub_id, " +
									"(select commit_date from assignment_submission where assignment_id = '"+assign_id+"' and user_group_id = '"+user_group_id+"') as commit_date, " +
									"(select commit_file_path from assignment_submission where assignment_id = '"+assign_id+"' and user_group_id = '"+user_group_id+"') as commit_file_path, " +
									"(select mark from assignment_submission where assignment_id = '"+assign_id+"' and user_group_id = '"+user_group_id+"') as mark " +
									"from assignment where assignment_id = '"+assign_id+"'");
			}else{
			
				rs1 = stmt2.executeQuery("select assignment_name, end_date, " +
									"(select assignment_sub_id from assignment_submission where assignment_id = '"+assign_id+"' and commit_user = '"+st_user_id+"') as ass_sub_id, " +
									"(select commit_date from assignment_submission where assignment_id = '"+assign_id+"' and commit_user = '"+st_user_id+"') as commit_date, " +
									"(select commit_file_path from assignment_submission where assignment_id = '"+assign_id+"' and commit_user = '"+st_user_id+"') as commit_file_path, " +
									"(select mark from assignment_submission where assignment_id = '"+assign_id+"' and commit_user = '"+st_user_id+"') as mark " +
									"from assignment where assignment_id = '"+assign_id+"'");
			}
			while(rs1.next())
			{
			  %>
			   <tr>
				<td><a href="<%=rs1.getString("commit_file_path")%>"><%= rs1.getString("assignment_name")%></a></td>
				<td><%= rs1.getString("end_date")%></td>
				<td><%=rs1.getString("commit_date")%></td>
				<td>
					<%
						
						String _mark = rs1.getString("mark");
						String _assignment_sub_id = rs1.getString("ass_sub_id");
						String _commit_date = rs1.getString("commit_date");
						if(_mark != null && !_mark.equals("0")){
							out.println("<a href='assignmentMarkDetail.jsp?assignmentSubID="+_assignment_sub_id+"&assignmentName="+assign_name+"'>review</a>");
						}
						else{
							if (_commit_date != null && _commit_date != "")
								out.println("<a href='tuStudentMarking.jsp?assignmentID="+assign_id+"&assignmentSubID="+_assignment_sub_id+"&assignmentName="+assign_name+"'>marking</a>");
							else
								out.println("<a>marking</a>");
						}
					%>
				</td>
			   </tr>
			  <%
			}
         }
        %>
       </table>
                        </td>
     </tr>
    </table>
   </div>
  </td>
 </tr>
</table>
</form>
<%
}
 }catch(Exception ex){
  out.println(ex);
 }
%>
</body>
</html>